﻿DROP INDEX [IX_Holiday_StateProvinceId] ON [dbo].[Holiday];
ALTER TABLE [dbo].[Holiday] DROP CONSTRAINT [FK__Holiday__StatePr__5A4F643B];
ALTER TABLE [dbo].[Holiday] DROP COLUMN [StateProvinceId];


CREATE NONCLUSTERED INDEX [IX_LogisticsLocation_AddressId]
    ON [dbo].[LogisticsLocation]([AddressId] ASC);
CREATE NONCLUSTERED INDEX [IX_LogisticsLocation_DistrictId]
    ON [dbo].[LogisticsLocation]([DistrictId] ASC);
CREATE NONCLUSTERED INDEX [IX_LogisticsLocation_PackageId]
    ON [dbo].[LogisticsLocation]([PackageId] ASC);
CREATE NONCLUSTERED INDEX [IX_LogisticsLocation_PlanId]
    ON [dbo].[LogisticsLocation]([PlanId] ASC);

ALTER TABLE [dbo].[Order] DROP CONSTRAINT [FK__Order__InvoiceId__361203C5];
CREATE NONCLUSTERED INDEX [IX_Order_Invoice]
    ON [dbo].[Order]([InvoiceId] ASC);
ALTER TABLE [dbo].[Order] WITH NOCHECK
    ADD CONSTRAINT [Order_Invoice] FOREIGN KEY ([InvoiceId]) REFERENCES [dbo].[Invoice] ([Id]);

ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [FK__Invoice__Custome__3429BB53];
CREATE NONCLUSTERED INDEX [IX_Invoice_InvoiceStatusId]
    ON [dbo].[Invoice]([InvoiceStatusId] ASC);
ALTER TABLE [dbo].[Invoice] WITH NOCHECK
    ADD CONSTRAINT [Invoice_Customer] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([Id]);

ALTER TABLE [dbo].[LogisticsLocation] DROP CONSTRAINT [LogisticsLocation_Package];


CREATE TABLE [dbo].[InvoiceNote] (
    [Id]                INT            IDENTITY (1, 1) NOT NULL,
    [InvoiceId]         INT            NOT NULL,
    [Note]              NVARCHAR (MAX) NOT NULL,
    [DisplayToCustomer] BIT            NOT NULL,
    [CreatedOnUtc]      DATETIME       NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [InvoiceNote_Invoice] FOREIGN KEY ([InvoiceId]) REFERENCES [dbo].[Invoice] ([Id]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_InvoiceNote_InvoiceId]
    ON [dbo].[InvoiceNote]([InvoiceId] ASC);

DELETE FROM Region where Id = 4
GO
SET IDENTITY_INSERT [dbo].[Region] ON
INSERT INTO [Region] (Id, Name, DisplayOrder) VALUES (3, N'Edmonton', 2);
SET IDENTITY_INSERT [dbo].[Region] OFF

SELECT * FROM Region

GO
SET IDENTITY_INSERT [dbo].[LogisticsDistrict] ON
INSERT INTO [LogisticsDistrict] (Id, RegionId, StateProvinceId, Name, DisplayOrder, CountryCode, Code, TimeZoneId) VALUES (3, 3, 1, N'Edmonton', 2, N'CA', N'EDM', N'Mountain Standard Time')
SET IDENTITY_INSERT [dbo].[LogisticsDistrict] OFF

SELECT * FROM [LogisticsDistrict]

GO
TRUNCATE TABLE dbo.[LogisticsZone];

GO
ALTER TABLE [dbo].[LogisticsZone] DROP CONSTRAINT [FK__Logistics__State__7AF13DF7];
ALTER TABLE dbo.[LogisticsZone] DROP COLUMN [StateProvinceId];
ALTER TABLE dbo.[LogisticsZone] DROP COLUMN [CountryCode];
ALTER TABLE dbo.[LogisticsZone] DROP COLUMN [DisplayOrder];

GO
SET IDENTITY_INSERT [dbo].[LogisticsZone] ON
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (1, N'V7W|V7S|V7V|V7T|V7R|V7P|V7M|V7N|V7K|V7L|V7J|V7H|V7G', N'00101', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (2, N'V6G|V6E|V7X|V6C|V7Y|V6Z', N'00102', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (3, N'V6B|V6A|V5L|V5K', N'00103', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (4, N'V6T|V6R|V6K|V6S|V6N|V6L|V6J|V6H|V6M', N'00104', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (5, N'V5Z|V5Y|V5T|V5V|V5N|V5M|V5R', N'00105', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (6, N'V5W|V5X|V6P|V7B|V6X|V6Y|V7A|V7E|V7C', N'00106', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (7, N'V5S|V5G|V5H|V5J|V5E|V3N', N'00107', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (8, N'V3J|V3K|V5A|V5B|V5C', N'00108', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (9, N'V3H|V3E|V3B|V3C|V3Y|V2X|V4R|V2W', N'00109', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (10, N'V5P|V6V|V6W|V3M|V3L', N'00110', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (11, N'V4C|V3W|V4E|V4G|V4K|V4L|V4M|V3V|V3T', N'00111', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (12, N'V3S|V4P|V4A|V4B|V3X', N'00112', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (13, N'V4N|V2Y|V3A|V2Z|V4W|V3R|V1M', N'00113', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (14, N'V4S|V2V|V4X|V2T|V2S|V3G|V2R|V2P|V4Z', N'00114', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (15, N'T3N|T3J|T1Y|T2A|T3K|T2E|T2M|T2K|T2L|T3L|T3G|T3A|T3B|T2N|T2P|T2R|T3C|T3H', N'00201', 2, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (16, N'T3S|T2B|T2C|T2Z|T3M|T2G|T2H|T2J|T2X|T2S|T2V|T2T|T3E|T2W|T2Y', N'00202', 2, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (17, N'T8N|T5Y|T5A|T5W|T5Z|T5C|T5B|T5H|T5J|T5K|T5E|T5G|T5X|T6V|T5L|T5M|T5N|T5R|T5P|T5V|T5S|T5T|T6M', N'00301', 3, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (18, N'T6S|T6P|T6T|T6A|T6B|T6L|T6X|T6C|T6E|T6K|T6N|T6G|T6H|T6J|T6R|T6W|T8H|T8A', N'00302', 3, NULL)
SET IDENTITY_INSERT [dbo].[LogisticsZone] OFF
GO


select * from [LogisticsZone]

select * from LogisticsWarehouse

INSERT INTO [LogisticsWarehouse] (AddressId, DistrictId) VALUES (46726, 3)